/*
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
http://ola.hallengren.com/
*/

USE master;
GO

-- rebuild or reorganize fragmented indexes and update modified statistics
-- in the database(s)
EXECUTE dbo.IndexOptimize @Databases = '<database_name(s)>',  --'USER_DATABASES'
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10, 
@FragmentationLevel2 = 30,
-- @SortInTempdb = 'Y',  -- sorting in tempdb when offline rebuilding an index
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample = 100,
-- @StatisticsResample = 'N',
	-- Y  Update statistics with the most recent sample.
	-- N  Let SQL Server automatically compute the required sample. This is the default.
@MaxDOP = 4,
@LogToTable='Y';
 
-- check the physical integrity of all user databases
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@MaxDOP = 4,
@PhysicalOnly = 'Y'; -- @ExtendedLogicalChecks = 'Y'  -- perform extended logical checks

/*
DECLARE @index_id int =
	(SELECT index_id FROM sys.indexes
		WHERE object_id = OBJECT_ID('Production.Product')
			AND name = 'AK_Product_Name');
DBCC CHECKTABLE ('Production.Product', @index_id);
*/

-- process a return value with DBCC CheckDB
BEGIN TRY
	DECLARE @ReturnValue INT;
	EXECUTE @ReturnValue = sp_executesql N'DBCC CHECKDB(''<database_name>'')';
	IF @ReturnValue <> 0
		RAISERROR ('Database corruption detected', 11, 1);
END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_STATE() AS ErrorState,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH

 -- back up all user databases, using checksums and compression, and verify the backup
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'D:\MSSQL\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@CheckSum = 'Y',
@Compress = 'Y',
@BufferCount = 50,
@MaxTransferSize = 4194304,
@NumberOfFiles = 64,
@CleanupTime = 24;